package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.LinkedList;
import java.util.List;

import servicios.Servicio;
import cuadrantes.Turno;

public class JDBCTurnoDAO implements IturnoDAO{
	
private Connection conn;
	
	public JDBCTurnoDAO(){
		
		conn = ConnectionManager.getInstance().checkOut();		
		
	}

	@Override
	public void delete(Turno t) {
		String sql = "DELETE FROM turnos WHERE (horaini = ? AND horafin = ?) ";
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setTime(1, t.getHoraini());
            stmt.setTime(2, t.getHorafin());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
	}

	@Override
	public void deleteserviciosturnos(Servicio s) {
		String sql = "DELETE FROM serviciosturnos WHERE (servicio = ? AND dnicliente = ?) ";
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, s.getNombre());
            stmt.setString(2, s.getCIF());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public void insert(Turno t) {
		String sql = "INSERT INTO turnos (tipo, horaini, horafin) VALUES (?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, t.getTurno());           
            stmt.setTime(2, t.getHoraini());
            stmt.setTime(3, t.getHorafin());                     
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}
		

	@Override
	public void insertserviciosturnos(Turno t, Servicio s, String d) {
		String sql = "INSERT INTO serviciosturnos (dnicliente, servicio, horaini, horafin, desc) VALUES (?, ?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, s.getCIF());
            stmt.setString(2, s.getNombre());
            stmt.setTime(3, t.getHoraini());
            stmt.setTime(4, t.getHorafin());
            stmt.setString(5, d);
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public Turno select(Time horaini, Time horafin) {
		PreparedStatement stmt = null;
        ResultSet result = null;        
        Turno t = new Turno();        
        String sql = "SELECT * FROM turnos WHERE(horaini = ? AND horafin = ?)";   
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setTime(1, horaini);
            stmt.setTime(2, horafin);
            result = stmt.executeQuery();
            if (result.next()) {
                t.setHoraini(horaini);
                t.setHorafin(horafin);
                t.setTurno(result.getString("desc"));
            	 
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return t;
	}

	@Override
	public List<String> selecttodoslosturnos() {
		PreparedStatement stmt = null;
        ResultSet result = null;       
        List<String> listaturnos = new LinkedList<String>();       
        String sql = "SELECT * FROM turnos";    
        try {
            stmt = conn.prepareStatement(sql);
            result = stmt.executeQuery();
 
            while (result.next()) {
                String s="";
                s=s+""+result.getString("horaini");
                s=s+" - "+result.getString("horafin");
                s=s+" - "+result.getString("desc");
                
                listaturnos.add(s);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return listaturnos;
	}

	@Override
	public List<Turno> selectturnosservicios(Servicio s) {
		PreparedStatement stmt = null;
        ResultSet result = null;        
        List<Turno> listaturnos = new LinkedList<Turno>();
        Turno t = new Turno(); 
       
        String sql = "SELECT * FROM serviciosturnos st, turnos t WHERE (st.horaini = t.horaini " +
        		"AND st.horafin = t.horafin " +
        		"AND st.servicio = ? " +
        		"AND st.dnicliente = ?); ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, s.getNombre());
            stmt.setString(2, s.getCIF());
            result = stmt.executeQuery();

            while(result.next()){            	         	
            	t.setHoraini(result.getTime("horaini"));
            	t.setHorafin(result.getTime("horafin"));
            	t.setTurno(result.getString("desc"));           	
            	listaturnos.add(t);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return listaturnos;
	}
	
	public void finalize(){
		ConnectionManager.getInstance().checkIn(conn);
		
	}

}
